package me.send.data.service.provider;

import java.util.Map;

import me.send.business.base.Pager;

public class BrokerPrizeProvider {
    
    public String list(Map<String, Object> param) {
    	String sql="";
    	if (param != null) {
    		String phone = (String) param.get("phone");
    		String realname = (String) param.get("realname");
    		String city = (String) param.get("city");
    		String start=(String) param.get("start");
    		String end=(String) param.get("end");
    		Pager page=(Pager) param.get("page");
    		
    	sql += " select p.cityname,p.telephone,p.username,p.real_name, (select count(1) from orders o where o.brokerid=p.popid and o.`status`>7   ";
    	if(start!=null&&!start.equals("")){
    		sql +=" and o.createtime>='"+start+"'";
		}
		if(end!=null&&!end.equals("")){
			sql +=" and o.createtime<'"+end+"'";
		}
    			sql+= " )  as orderCount ,(select COUNT(1) from broker_evaluate mie LEFT JOIN orders mio on mie.orderid=mio.orderid where mio.brokerid=p.popid and mie.point<1.5 ";
    	if(start!=null&&!start.equals("")){
    	    sql +=" and mio.createtime>='"+start+"'";
    	}
    	if(end!=null&&!end.equals("")){
    		sql +=" and mio.createtime<'"+end+"'";
    	}
    			sql+=" ) as minEvaluateCount ,(select COUNT(1) from broker_evaluate mie LEFT JOIN orders mio on mie.orderid=mio.orderid where mio.brokerid=p.popid and mie.point>=1.5 and mie.point<3 ";
    			if(start!=null&&!start.equals("")){
    	    	    sql +=" and mio.createtime>='"+start+"'";
    	    	}
    	    	if(end!=null&&!end.equals("")){
    	    		sql +=" and mio.createtime<'"+end+"'";
    	    	}				
    	    	sql+=" ) as midEvaluateCount ,(select COUNT(1) from broker_evaluate mie LEFT JOIN orders mio on mie.orderid=mio.orderid where mio.brokerid=p.popid and mie.point>=3 ";
	if(start!=null&&!start.equals("")){
    sql +=" and mio.createtime>='"+start+"'";
	}
	if(end!=null&&!end.equals("")){
	sql +=" and mio.createtime<'"+end+"'";
	}
	sql+=" ) as maxEvaluateCount from popuser p where 1=1 ";
    	if (phone!=null&&!phone.equals("")) {
			sql += " and p.telephone=" + phone;
		}
    	if (realname!=null&&!realname.equals("")) {
    		sql += " and p.real_name like" + realname;
    	}
    	if (city!=null&&!city.equals("")) {
    		sql += " and p.cityname like " + city;
    	}
    	sql += " limit " + page.getStart() + "," + page.getRows();
    	}
    	return sql;
    }
    
    public String count(Map<String, Object> param) {
    	String sql="";
    	if (param != null) {
    		String phone = (String) param.get("phone");
    		String realname = (String) param.get("realname");
    		String city = (String) param.get("city");
    		
    		sql += " select count(1) from popuser p where 1=1 ";
    		if (phone!=null&&!phone.equals("")) {
    			sql += " and p.telephone=" + phone;
    		}
    		if (realname!=null&&!realname.equals("")) {
    			sql += " and p.real_name=" + realname;
    		}
    		if (city!=null&&!city.equals("")) {
    			sql += " and p.cityname=" + city;
    		}
    	}
    	return sql;
    }
    
    
    
}
